-- 创建配置表 (Setting Table)
-- 使用KV结构存储各种系统配置
CREATE TABLE IF NOT EXISTS setting (
    id BIGSERIAL PRIMARY KEY,
    key VARCHAR(100) NOT NULL UNIQUE, -- 配置键（唯一）
    value TEXT, -- 配置值（JSON或字符串）
    description VARCHAR(500), -- 配置描述
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 更新时间
);

-- 创建索引
CREATE INDEX idx_setting_key ON setting (key);
CREATE INDEX idx_setting_created_time ON setting (created_time);

-- 创建更新时间触发器函数（如果不存在）
CREATE OR REPLACE FUNCTION update_updated_time_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为setting表创建更新时间触发器
CREATE TRIGGER update_setting_updated_time 
    BEFORE UPDATE ON setting 
    FOR EACH ROW EXECUTE FUNCTION update_updated_time_column();

-- 添加表注释
COMMENT ON TABLE setting IS '系统配置表（KV结构）';
COMMENT ON COLUMN setting.id IS '主键ID';
COMMENT ON COLUMN setting.key IS '配置键（唯一）';
COMMENT ON COLUMN setting.value IS '配置值（JSON或字符串）';
COMMENT ON COLUMN setting.description IS '配置描述';
COMMENT ON COLUMN setting.created_time IS '创建时间';
COMMENT ON COLUMN setting.updated_time IS '更新时间';

-- 授予权限给应用用户（根据实际用户名修改）
-- 请将 'taosiyu' 替换为你的实际数据库用户名
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE setting TO taosiyu;
GRANT USAGE, SELECT ON SEQUENCE setting_id_seq TO taosiyu;

-- 如果有其他用户需要访问，也可以授权
-- GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE setting TO other_user;
-- GRANT USAGE, SELECT ON SEQUENCE setting_id_seq TO other_user;

-- 插入默认的页脚配置示例（可选）
-- INSERT INTO setting (key, value, description) 
-- VALUES (
--     'page_footer_setting', 
--     '{"title":"欢迎访问","content":"关于我们的介绍","bilibiliUrl":"https://www.bilibili.com","wechatUrl":"","wechatCode":"","wechatCodeUrl":"","logoUrl":"","copyright":"© 2025 All Rights Reserved","weiboUrl":"","email":"contact@example.com","phone":"400-123-4567","wechat":"wxid_example"}',
--     '页脚配置信息'
-- );

